import pymysql
import simplejson

"""
1、连接本地数据库
2、建立游标
3、创建表
4、插入表数据、查询表数据、更新表数据、删除表数据
"""

def create_table():
    #连接本地数据库
    db = pymysql.connect(host="localhost", user="root", password="123456", db="t39",port=3306)

    #创建游标
    cursor = db.cursor()

    #创建student表
    sql1 = """
        create table student(
        studentId int primary key,
        name char(10),
        age int
        )
    """

    #创建score表
    sql2 = """
        create table score(
        Chinese char(10),
        Math char(10),
        English char(10),
        scoreId int,
        foreign key(scoreId) references student(studentId) 
        )
    """
    sql3 = """INSERT INTO student(studentId,name,AGE) VALUES (%s,%s,%s)"""
    sql4 = """INSERT INTO score(scoreId,Chinese,Math,English) VALUES (%s,%s,%s,%s)"""

    val3 = ((1,'王一涵',10),(2,'张青阳',12),(3,'韩名博',12),(4,'王梓',13),(5,'骆铭峰',11),(6,'赢乘风',11),(7,'林烽',10),(8,'吴博文',12),(9,'马小文',12))
    # val3 = (('王一涵',10),('张青阳',12),('韩名博',12),('王梓',13),('骆铭峰',11),('赢乘风',11),('林烽',10),('吴博文',12),('马小文',12))
    val4 = ((1,90,80,75),(2,95,90,98),(3,80,89,70),(4,60,75,65),(5,81,82,85),(6,78,89,65),(7,89,60,49),(8,89,92,79),(9,50,60,62))

    try:
        # 执行SQL语句
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.executemany(sql3,val3)
        cursor.executemany(sql4,val4)
        db.commit()
        print("创建数据库成功")

    except Exception as e:
        print("创建数据库失败：case%s"%e)
    finally:
        #关闭游标连接
        db.commit()
        cursor.close()
        # 关闭数据库连接
        db.close()

def main():
    create_table()

if __name__ == "__main__":
    main()
